package com.russell.pms.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.russell.pms.dao.PropertyDao;
import com.russell.pms.model.Property;

public class PropertyDaoJDBC extends BaseMysqlDaoJDBC implements PropertyDao {

	private static final class PropertyRowMapper implements RowMapper<Property> {

		@Override
		public Property mapRow(ResultSet rs, int rowNum) throws SQLException {
			Property property = new Property();
			property.setId(rs.getInt("PROP_ID"));
			property.setDescription(rs.getString("DESCRIPTION"));
			property.setCity(rs.getString("CITY"));
			property.setStreet(rs.getString("STREET"));
			property.setState(rs.getString("STATE"));
			property.setZip(rs.getString("ZIP"));

			property.setDateModified(rs.getDate("MODIFIED_DATE"));
			property.setDateCreated(rs.getDate("CREATED_DATE"));
			property.setModifiedBy(rs.getString("MODIFIED_BY"));

			return property;
		}

	}

	@Override
	public List<Property> getProperties() {
		List<Object> params = new ArrayList<Object>();
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT");
		sql.append(" PROP_ID, DESCRIPTION, STREET, CITY, STATE, ZIP,");
		sql.append(" CREATED_DATE, MODIFIED_BY, MODIFIED_DATE");
		sql.append(" FROM");
		sql.append(" pms_app.PROP");
		sql.append(" ORDER BY PROP_ID");

		List<Property> propertyList = getJdbcTemplate().query(sql.toString(),
				new PropertyRowMapper(), params.toArray());
		return propertyList;
	}

	@Override
	public Property getProperty(int id) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT");
		sql.append(" PROP_ID, DESCRIPTION, STREET, CITY, STATE, ZIP,");
		sql.append(" CREATED_DATE, MODIFIED_BY, MODIFIED_DATE");
		sql.append(" FROM");
		sql.append(" pms_app.PROP");
		sql.append(" WHERE");
		sql.append(" PROP_ID = :id");

		MapSqlParameterSource paramSource = new MapSqlParameterSource();
		paramSource.addValue("id", id);

		try {
			Property property = getNamedParameterJdbcTemplate().queryForObject(
					sql.toString(), paramSource, new PropertyRowMapper());

			return property;
		} catch (EmptyResultDataAccessException e) {
			logger.warn("Could not find in PROP " + id + " " + e.getMessage());
			return null;

		}
	}

	@Override
	public void removeProperty(Property property) {
		StringBuilder sql = new StringBuilder();
		sql.append("DELETE FROM pms_app.PROP");
		sql.append(" WHERE");
		sql.append(" PROP_ID = :id");
		Map<String, ?> namedParameters = Collections.singletonMap("id",
				property.getId());
		getNamedParameterJdbcTemplate().update(sql.toString(), namedParameters);

	}

	@Override
	public void saveProperty(final Property property) {
		final StringBuilder sql = new StringBuilder();
		sql.append("INSERT INTO pms_app.PROP");
		sql.append(" (DESCRIPTION, STREET, CITY, STATE, ZIP,");
		sql.append(" CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE)");
		sql.append(" VALUES(?,?,?,?,?,?,?,?,?)");

		KeyHolder keyHolder = new GeneratedKeyHolder();

		getJdbcTemplate().update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(
					Connection connection) throws SQLException {
				PreparedStatement ps = connection.prepareStatement(
						sql.toString(), new String[] { "prop_id" });
				ps.setString(1, property.getDescription());
				ps.setString(2, property.getStreet());
				ps.setString(3, property.getCity());
				ps.setString(4, property.getState());
				ps.setString(5, property.getZip());
				ps.setString(6, property.getCreatedBy());
				Timestamp dateCreated = null;
				if (property.getDateCreated() != null) {
					dateCreated = new Timestamp(property.getDateCreated()
							.getTime());
				}
				ps.setTimestamp(7, dateCreated);
				ps.setString(8, property.getModifiedBy());
				Timestamp dateModified = null;
				if (property.getDateCreated() != null) {
					dateModified = new Timestamp(property.getDateCreated()
							.getTime());
				}
				ps.setTimestamp(9, dateModified);
				return ps;
			}

		}, keyHolder);

		property.setId(keyHolder.getKey().intValue());

	}

	@Override
	public void updateProperty(Property property) {
		StringBuilder sql = new StringBuilder();
		sql.append("UPDATE pms_app.PROP SET");
		sql.append(" DESCRIPTION=?, STREET=?, CITY=?, STATE=?, ZIP=?,");
		sql.append(" CREATED_BY=?, CREATED_DATE=?, MODIFIED_BY=?, MODIFIED_DATE=?");
		sql.append(" WHERE");
		sql.append(" PROP_ID=?");

		List<Object> params = new ArrayList<Object>();
		params.add(property.getDescription());
		params.add(property.getStreet());
		params.add(property.getCity());
		params.add(property.getState());
		params.add(property.getZip());
		params.add(property.getCreatedBy());
		params.add(property.getDateCreated());
		params.add(property.getModifiedBy());
		params.add(property.getDateModified());
		params.add(property.getId());

		getJdbcTemplate().update(sql.toString(), params.toArray());

	}

}
